/// <summary>
//                                                                      //
//  Developer   : Wicus Botha                                           //
//                                                                      //
//  Description :                                                       //
//                                                                      //
//  History     : WB - 2008-10-20 - Created                             //
//                                                                      //
/// </summary>

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;

namespace IQ.DbBase
{
    public static class DbUtil
    {
        #region - Private methods -
        private static string getFilterValue(string type, string fldValue, string prefix)
        {
            switch (type)
            {
                case "System.String": return "LIKE '" + prefix + fldValue + "%'";
                case "System.Boolean":
                case "System.Byte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Decimal":
                case "System.Single":
                case "System.Double": return "=" + fldValue;
                case "System.DateTime":
                    {
                        // This will be invalid date
                        if (fldValue.Length < 8)
                            return "";
                        else
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(fldValue, out tmp))
                            {
                                return "='" + tmp.ToShortDateString() + "'";
                            }
                            else
                                return "";
                        }
                    }
                default: return "";
            }
        }
        #endregion

        #region - Public methods -
        #region - Get Field ... -
        /// <summary>
        /// Get a Db Field type
        /// </summary>
        public static DbFieldType GetFieldType(string dataType)
        {
            switch (dataType)
            {
                case "System.Char": return DbFieldType.Char;
                case "System.String": return DbFieldType.String;
                case "System.Boolean": return DbFieldType.Boolean;
                case "System.Byte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64": return DbFieldType.Integer;
                case "System.Decimal":
                case "System.Single":
                case "System.Double": return DbFieldType.Decimal;
                case "System.DateTime": return DbFieldType.DateTime;
                default: return DbFieldType.Unknown;
            }
        }
        /// <summary>
        /// Get a Db Field value, if the value == DbNull it returns the defaultvalue
        /// </summary>
        public static object GetFieldValue(object value, object defaultValue)
        {
            if (value == DBNull.Value)
                return defaultValue;
            else
                return value;
        }
        /// <summary>
        /// Get a Db Field value as String, and default = empty string
        /// </summary>
        public static string GetFieldValueString(object value)
        {
            return Convert.ToString(GetFieldValue(value, ""));
        }
        /// <summary>
        /// Returns Db Field value with qoutes or not, according to the DbType
        /// </summary>
        public static string GetFieldValueQuoted(string type, string fldValue)
        {
            switch (type)
            {
                case "System.Char":
                case "System.String": return "'" + fldValue + "'";
                case "System.Boolean":
                case "System.Byte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Decimal":
                case "System.Single":
                case "System.Double": return fldValue;
                case "System.DateTime":
                    {
                        // 2010-03-21 12:20:30 AM
                        if (fldValue.Length >= 10)
                            return "'" + fldValue + "'";
                        else
                            return "";
                    }
                default: return "";
            }
        }
        /// <summary>
        /// Returns Db Field value with qoutes or not, and in the correct format for Sql string
        /// </summary>
        public static string GetDbString(object property)
        {
            if (property == null)
            {
                return "NULL";
            }
            else if (property.GetType() == typeof(System.DateTime))
            {
                return "'" + Convert.ToDateTime(property).ToString("yyyyMMdd hh:mm:ss tt") + "'";
            }
            else if (property.GetType() == typeof(System.Char) ||
                     property.GetType() == typeof(System.String))
                return "'" + property.ToString() + "'";
            else
            {
                return property.ToString();
            }
        }
        #endregion

        #region - Check Values -
        public static bool CheckForNumbersOnly(string value)
        {
            try
            {
                Convert.ToDouble(value);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool CheckTypeAndValue(string type, string fldValue)
        {
            if (type != "System.String" && type != "System.DateTime")
            {
                return CheckForNumbersOnly(fldValue);
            }
            else
                return true;
        }
        public static string CheckForStringQuote(String value)
        {
            return value.Replace("'", "''");
        }
        #endregion

        #region - Filter data -
        public static string EscapeLikeValue(string filterValue)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < filterValue.Length; i++)
            {
                char c = filterValue[i];
                if (c == '*' || c == '%' || c == '[' || c == ']' || c == '|')
                    sb.Append("[").Append(c).Append("]");
                else if (c == '\'')
                    sb.Append("''");
                else
                    sb.Append(c);
            }
            return sb.ToString();
        }
        public static string GetFilterValue(string type, string fldValue, bool any)
        {
            if (any)
                return getFilterValue(type, fldValue, "%");
            else
                return getFilterValue(type, fldValue, "");
        }
        public static string GetFilterValue(string type, string fldValue)
        {
            return getFilterValue(type, fldValue, "");
        }
        public static string GetFilterValueDateBetween(string fldName, string fldValue)
        {
            // This will be invalid date
            if (fldValue.Length < 8)
                return "";
            else
            {
                DateTime tmp;
                if (DateTime.TryParse(fldValue, out tmp))
                {
                    return ">='" + tmp.ToShortDateString() + " 00:00:00' AND " + fldName + "<='" + tmp.ToShortDateString() + " 23:59:59'";
                }
                else
                    return "";
            }
        }
        /// <summary>
        /// Filter DataTable on DefaultView
        /// </summary>
        public static void FilterData(DataTable dt, int colIndex, string operatorStr, string filterValue)
        {
            if (filterValue.Length == 0)
                dt.DefaultView.RowFilter = "";
            else
            {
                string filter = dt.Columns[colIndex].ColumnName + " ";
                string filterVal = GetFieldValueQuoted(dt.Columns[colIndex].DataType.ToString(), filterValue);
                if (filterVal != "")
                {
                    if (operatorStr.ToLower() == "like" &&
                        dt.Columns[colIndex].DataType == typeof(System.String))
                    {
                        filterVal = filterVal.Insert(filterVal.Length - 1, "%");
                    }
                    filter += operatorStr + " " + filterVal;
                }
                else
                    filter = "";
                //
                dt.DefaultView.RowFilter = filter;
            }
        }
        /// <summary>
        /// Filter DataTable on DefaultView
        /// </summary>
        public static void FilterDataTable(DataTable table, string selectedField, string filterValue)
        {
            if (filterValue.Length == 0)
                table.DefaultView.RowFilter = "";
            else
            {
                filterValue = EscapeLikeValue(filterValue);
                if (table.Columns[selectedField].DataType == typeof(string) ||
                    table.Columns[selectedField].DataType == typeof(char))
                {
                    table.DefaultView.RowFilter = selectedField + " like '" + filterValue + "%'";
                }
                else if (table.Columns[selectedField].DataType == typeof(DateTime))
                {// 2009-11-19
                    if (filterValue.Length == 10)
                    table.DefaultView.RowFilter = selectedField + ">#"
                            + filterValue + " 00:00:00# AND " + selectedField + "<#" + filterValue + " 23:59:59#"
                            + " OR " + selectedField + "=#" + filterValue + "#";
                }
                else
                {
                    table.DefaultView.RowFilter = selectedField + "=" + filterValue;
                }                
            }
        }
        #endregion

        #region - Defaults For - ? -
        public static string GetDefaultFor(DbFieldType fieldType, object value)
        {
            switch (fieldType)
            {
                case DbFieldType.String: return GetDefaultForString(value.ToString());
                case DbFieldType.DateTime: return (value == DBNull.Value ?  GetDefaultForDate(null) : GetDefaultForDate(Convert.ToDateTime(value)));
                case DbFieldType.Char: return GetDefaultForChar((Char)value);
                default: return GetDefaultForObject(value);
            }
        }
        public static string GetDefaultForDate(DateTime? value)
        {
            if (value == null)
                return "default";
            else
            {
                DateTime val = value.GetValueOrDefault();
                if (val.Year == 1)
                    return "default";
                else
                    return "'" + val.ToString("yyyyMMdd HH:mm:ss") + "'";
            }
        }
        public static string GetDefaultForDateEndOfDay(DateTime? value)
        {
            if (value == null)
                return "default";
            else
            {
                DateTime val = value.GetValueOrDefault();
                if (val.Year == 1)
                    return "default";
                else
                    return "'" + val.ToString("yyyyMMdd 23:59:59") + "'";
            }
        }
        public static string GetDefaultForDateOnly(DateTime? value)
        {
            if (value == null)
                return "default";
            else
            {
                DateTime val = value.GetValueOrDefault();
                if (val.Year == 1)
                    return "default";
                else
                    return "'" + val.ToString("yyyyMMdd") + "'";
            }
        }
        public static string GetDefaultForChar(char value)
        {
            return "'" + value + "'";
        }
        public static string GetDefaultForString(object value)
        {
            if (value == null)
                return "default";
            else
            {
                return "'" + value.ToString().Replace("'", "''") + "'";
            }
        }
        public static string GetDefaultForString(String value)
        {
            if (value == null)
                return "default";
            else
            {
                return "'" + value.Replace("'", "''") + "'";
            }
        }
        public static string GetDefaultForObject(Object value)
        {
            if (value == null)
                return "default";
            else
            {
                return value.ToString();
            }
        }
        #endregion

        /// <summary>
        /// Takes out all the NULL fields in the Update Sql string
        /// </summary>
        public static string GetUpdateNoNULL(string sqlUpdate)
        {
            int posNULL = sqlUpdate.IndexOf("NULL");
            int posComma = 0;
            while (posNULL > 0)
            {
                posComma = sqlUpdate.LastIndexOf(",", posNULL);
                sqlUpdate = sqlUpdate.Remove(posComma, posNULL + 4 - posComma);
                posNULL = sqlUpdate.IndexOf("NULL");
            }
            return sqlUpdate;
        }
        public static Object[] LoadDataOjectArray(string sql, Type type)
        {
            DataTable dt = DbHelper.Load_Table(sql);
            Object[] objList = (Object[])Array.CreateInstance(type, dt.Rows.Count);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                objList[i] = Activator.CreateInstance(type);
                PropertyLoader.LoadDataIntoProperties(objList[i], dt.Rows[i]);
            }
            return objList;
        }
        public static bool CheckIfTableChanged(DataTable dt)
        {
            if (dt == null)
                return false;
            else
            {
                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    if (dt.Rows[i].RowState != DataRowState.Unchanged && dt.Rows[i].RowState != DataRowState.Detached)
                        return true;
                }
            }
            return false;
        }
        public static int GetTableChangeCount(DataTable dt)
        {
            if (dt == null)
                return 0;
            else
            {
                DataTable dtChanges = dt.GetChanges();
                if (dtChanges != null && dtChanges.Rows != null)
                    return dtChanges.Rows.Count;
                else
                    return 0;
            }
        }
        public static DataTable GetTableFromIEnum(IEnumerable<object> objList)
        {
            DataTable dt = null;
            PropertyInfo[] objProperties = null;
            //
            foreach (object obj in objList)
            {
                // Create table & set properties
                if (dt == null)
                {
                    dt = GetTableSchemaFromObject(obj);
                    Type objType = obj.GetType();
                    objProperties = objType.GetProperties();
                }
                // Add table rows for each object item in list
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo property in objProperties)
                {
                    object value = property.GetValue(obj, null);
                    dr[property.Name] = value ?? DBNull.Value;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        public static DataTable GetTableSchemaFromObject(object obj)
        {
            DataTable dt = new DataTable();
            Type objType = obj.GetType();
            PropertyInfo[] objProperties = objType.GetProperties();
            foreach (PropertyInfo property in objProperties)
            {
                //You have to use the Nullable.GetUnderlyingType method because the datatable doesn't recognize nullable types.
                //If that returns null, then it is not a nullable type and the null-coalescing operator (??) will use the 
                //property type (LHS != null ? LHS : RHS;) => (LHS ?? RHS;) .
                dt.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
            }
            return dt;
        }

        #region - Table: Sum -
        /// <summary>
        /// Loop the table & total all the fields in the list
        /// </summary>
        /// <param name="dt">Table to summarize</param>
        /// <param name="fieldsToTotal">Dictionary with the fields to total, and their starting values</param>
        public static void SumDataTable(DataTable dt, Dictionary<string, decimal> fieldsToTotal)
        {
            List<string> keyList = new List<string>(fieldsToTotal.Keys);
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                foreach (string key in keyList)
                {
                    fieldsToTotal[key] += Convert.ToDecimal(dt.Rows[i][key]);
                }
            }
        }

        /// <summary>
        /// Loop the table & total all the fields in the list
        /// </summary>
        /// <param name="dt">Table to summarize</param>
        /// <param name="fieldsToTotal">Fields to total, comma separated. i.e. "Instalment, Capital, Count</param>
        /// <returns>Returns a dictionary with the totals</returns>
        public static Dictionary<string, decimal> SumDataTable(DataTable dt, string fieldsToTotal)
        {
            string[] fields = fieldsToTotal.Split(',');
            Dictionary<string, decimal> totals = new Dictionary<string, decimal>();
            for (int j = 0; j < fields.Length; ++j)
            {
                totals.Add(fields[j].Trim(), 0);
            }
            //
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                foreach (string field in fields)
                {
                    totals[field] += Convert.ToDecimal(dt.Rows[i][field]);
                }
            }
            return totals;
        }
        #endregion

        #region - Table: DataRow Helpers -
        public static void UpdateDataRowValue(DataRow dr, string fieldName, object value)
        {
            if (dr.Table.Columns.Contains(fieldName))
                dr[fieldName] = (value == null ? DBNull.Value : value);
        }
        //
        public static object GetDataRowValueCheckNull(DataRow dr, string fieldName, object nullValue, DataRowVersion version = DataRowVersion.Current)
        {
            if (dr.Table.Columns.Contains(fieldName))
                return (dr[fieldName, version] == DBNull.Value ? nullValue : dr[fieldName, version]);
            else
                return nullValue;
        }
        #endregion

        #endregion
    }
}
